-- PostgreSQL 初始化脚本（所有表添加前缀 blog_）
-- 目标数据库由 POSTGRES_DB 创建：blog_resume

-- 1. 用户表
CREATE TABLE IF NOT EXISTS blog_user (
    id              BIGSERIAL PRIMARY KEY,
    username        VARCHAR(50)  NOT NULL UNIQUE,
    password        VARCHAR(255) NOT NULL,
    email           VARCHAR(100),
    nickname        VARCHAR(50),
    avatar          VARCHAR(255),
    status          SMALLINT DEFAULT 1,
    create_time     TIMESTAMP DEFAULT NOW(),
    update_time     TIMESTAMP DEFAULT NOW()
);

-- 2. 分类表
CREATE TABLE IF NOT EXISTS blog_category (
    id              BIGSERIAL PRIMARY KEY,
    name            VARCHAR(50)  NOT NULL,
    description     VARCHAR(200),
    parent_id       BIGINT DEFAULT 0,
    sort_order      INT DEFAULT 0,
    status          SMALLINT DEFAULT 1,
    create_time     TIMESTAMP DEFAULT NOW(),
    update_time     TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_blog_category_parent ON blog_category(parent_id);
CREATE INDEX IF NOT EXISTS idx_blog_category_status ON blog_category(status);

-- 3. 标签表
CREATE TABLE IF NOT EXISTS blog_tag (
    id              BIGSERIAL PRIMARY KEY,
    name            VARCHAR(50) NOT NULL UNIQUE,
    color           VARCHAR(20) DEFAULT '#1890ff',
    create_time     TIMESTAMP DEFAULT NOW()
);

-- 4. 文章表
CREATE TABLE IF NOT EXISTS blog_article (
    id              BIGSERIAL PRIMARY KEY,
    title           VARCHAR(200) NOT NULL,
    content         TEXT,
    summary         VARCHAR(500),
    cover_image     VARCHAR(255),
    category_id     BIGINT,
    author_id       BIGINT NOT NULL,
    status          SMALLINT DEFAULT 1,
    view_count      INT DEFAULT 0,
    like_count      INT DEFAULT 0,
    comment_count   INT DEFAULT 0,
    seo_title       VARCHAR(200),
    seo_keywords    VARCHAR(500),
    seo_description VARCHAR(500),
    create_time     TIMESTAMP DEFAULT NOW(),
    update_time     TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_blog_article_category ON blog_article(category_id);
CREATE INDEX IF NOT EXISTS idx_blog_article_author ON blog_article(author_id);
CREATE INDEX IF NOT EXISTS idx_blog_article_status ON blog_article(status);
CREATE INDEX IF NOT EXISTS idx_blog_article_create_time ON blog_article(create_time);
CREATE INDEX IF NOT EXISTS idx_blog_article_status_time ON blog_article(status, create_time);

-- 全文搜索索引（PostgreSQL使用GIN索引）
CREATE INDEX IF NOT EXISTS idx_blog_article_fulltext ON blog_article USING GIN(to_tsvector('english', title || ' ' || COALESCE(content, '')));

-- 5. 文章标签关联表
CREATE TABLE IF NOT EXISTS blog_article_tag (
    id          BIGSERIAL PRIMARY KEY,
    article_id  BIGINT NOT NULL,
    tag_id      BIGINT NOT NULL,
    CONSTRAINT uk_blog_article_tag UNIQUE(article_id, tag_id)
);
CREATE INDEX IF NOT EXISTS idx_blog_article_tag_article ON blog_article_tag(article_id);
CREATE INDEX IF NOT EXISTS idx_blog_article_tag_tag ON blog_article_tag(tag_id);

-- 6. 评论表
CREATE TABLE IF NOT EXISTS blog_comment (
    id          BIGSERIAL PRIMARY KEY,
    article_id  BIGINT NOT NULL,
    user_id     BIGINT,
    parent_id   BIGINT DEFAULT 0,
    content     TEXT NOT NULL,
    nickname    VARCHAR(50),
    email       VARCHAR(100),
    status      SMALLINT DEFAULT 1,
    create_time TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_blog_comment_article ON blog_comment(article_id);
CREATE INDEX IF NOT EXISTS idx_blog_comment_user ON blog_comment(user_id);
CREATE INDEX IF NOT EXISTS idx_blog_comment_parent ON blog_comment(parent_id);
CREATE INDEX IF NOT EXISTS idx_blog_comment_status ON blog_comment(status);
CREATE INDEX IF NOT EXISTS idx_blog_comment_article_status ON blog_comment(article_id, status);

-- 7. 简历信息表
CREATE TABLE IF NOT EXISTS blog_resume (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT NOT NULL UNIQUE,
    name        VARCHAR(50)  NOT NULL,
    title       VARCHAR(100),
    summary     TEXT,
    avatar      VARCHAR(255),
    email       VARCHAR(100),
    phone       VARCHAR(20),
    location    VARCHAR(100),
    github      VARCHAR(255),
    linkedin    VARCHAR(255),
    website     VARCHAR(255),
    create_time TIMESTAMP DEFAULT NOW(),
    update_time TIMESTAMP DEFAULT NOW()
);

-- 8. 工作经验表
CREATE TABLE IF NOT EXISTS blog_work_experience (
    id          BIGSERIAL PRIMARY KEY,
    resume_id   BIGINT NOT NULL,
    company     VARCHAR(100) NOT NULL,
    position    VARCHAR(100) NOT NULL,
    start_date  DATE NOT NULL,
    end_date    DATE,
    current     SMALLINT DEFAULT 0,
    description TEXT,
    achievements TEXT,
    sort_order  INT DEFAULT 0,
    create_time TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_blog_work_experience_resume ON blog_work_experience(resume_id);
CREATE INDEX IF NOT EXISTS idx_blog_work_experience_sort ON blog_work_experience(sort_order);
CREATE INDEX IF NOT EXISTS idx_blog_work_experience_resume_sort ON blog_work_experience(resume_id, sort_order);

-- 9. 技能表
CREATE TABLE IF NOT EXISTS blog_skill (
    id          BIGSERIAL PRIMARY KEY,
    resume_id   BIGINT NOT NULL,
    name        VARCHAR(100) NOT NULL,
    category    VARCHAR(50),
    level       INT DEFAULT 1,
    description VARCHAR(500),
    sort_order  INT DEFAULT 0,
    create_time TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_blog_skill_resume ON blog_skill(resume_id);
CREATE INDEX IF NOT EXISTS idx_blog_skill_category ON blog_skill(category);
CREATE INDEX IF NOT EXISTS idx_blog_skill_resume_category ON blog_skill(resume_id, category);
CREATE INDEX IF NOT EXISTS idx_blog_skill_sort ON blog_skill(sort_order);

-- 10. 项目经验表
CREATE TABLE IF NOT EXISTS blog_project (
    id          BIGSERIAL PRIMARY KEY,
    resume_id   BIGINT NOT NULL,
    name        VARCHAR(200) NOT NULL,
    description TEXT,
    highlights  TEXT,
    technologies VARCHAR(500),
    github_url  VARCHAR(255),
    demo_url    VARCHAR(255),
    image_url   VARCHAR(255),
    start_date  DATE,
    end_date    DATE,
    sort_order  INT DEFAULT 0,
    create_time TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_blog_project_resume ON blog_project(resume_id);
CREATE INDEX IF NOT EXISTS idx_blog_project_sort ON blog_project(sort_order);
CREATE INDEX IF NOT EXISTS idx_blog_project_resume_sort ON blog_project(resume_id, sort_order);

-- 11. 教育背景表
CREATE TABLE IF NOT EXISTS blog_education (
    id          BIGSERIAL PRIMARY KEY,
    resume_id   BIGINT NOT NULL,
    school      VARCHAR(100) NOT NULL,
    major       VARCHAR(100) NOT NULL,
    degree      VARCHAR(50) NOT NULL,
    start_date  DATE NOT NULL,
    end_date    DATE,
    current     SMALLINT DEFAULT 0,
    gpa         VARCHAR(20),
    description TEXT,
    achievements TEXT,
    sort_order  INT DEFAULT 0,
    create_time TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_blog_education_resume ON blog_education(resume_id);
CREATE INDEX IF NOT EXISTS idx_blog_education_sort ON blog_education(sort_order);
CREATE INDEX IF NOT EXISTS idx_blog_education_resume_sort ON blog_education(resume_id, sort_order);

-- 12. 角色表
CREATE TABLE IF NOT EXISTS blog_role (
    id          BIGSERIAL PRIMARY KEY,
    code        VARCHAR(50)  NOT NULL UNIQUE,
    name        VARCHAR(100) NOT NULL,
    description VARCHAR(200),
    data_scope  VARCHAR(20) DEFAULT 'SELF',
    status      SMALLINT DEFAULT 1,
    create_time TIMESTAMP DEFAULT NOW(),
    update_time TIMESTAMP DEFAULT NOW()
);

-- 13. 权限表
CREATE TABLE IF NOT EXISTS blog_permission (
    id          BIGSERIAL PRIMARY KEY,
    code        VARCHAR(100) NOT NULL UNIQUE,
    name        VARCHAR(100) NOT NULL,
    type        VARCHAR(20) DEFAULT 'API',
    method      VARCHAR(10),
    path        VARCHAR(255),
    description VARCHAR(255),
    status      SMALLINT DEFAULT 1,
    create_time TIMESTAMP DEFAULT NOW()
);

-- 14. 用户-角色关联表
CREATE TABLE IF NOT EXISTS blog_user_role (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT NOT NULL,
    role_id     BIGINT NOT NULL,
    create_time TIMESTAMP DEFAULT NOW(),
    CONSTRAINT uk_blog_user_role UNIQUE(user_id, role_id)
);
CREATE INDEX IF NOT EXISTS idx_blog_user_role_user ON blog_user_role(user_id);
CREATE INDEX IF NOT EXISTS idx_blog_user_role_role ON blog_user_role(role_id);

-- 15. 角色-权限关联表
CREATE TABLE IF NOT EXISTS blog_role_permission (
    id           BIGSERIAL PRIMARY KEY,
    role_id      BIGINT NOT NULL,
    permission_id BIGINT NOT NULL,
    CONSTRAINT uk_blog_role_perm UNIQUE(role_id, permission_id)
);
CREATE INDEX IF NOT EXISTS idx_blog_role_permission_role ON blog_role_permission(role_id);
CREATE INDEX IF NOT EXISTS idx_blog_role_permission_perm ON blog_role_permission(permission_id);

-- 16. 系统配置表
CREATE TABLE IF NOT EXISTS blog_system_config (
    id           BIGSERIAL PRIMARY KEY,
    config_key   VARCHAR(100) NOT NULL UNIQUE,
    config_value TEXT,
    description  VARCHAR(200),
    create_time  TIMESTAMP DEFAULT NOW(),
    update_time  TIMESTAMP DEFAULT NOW()
);

-- 17. 操作日志表
CREATE TABLE IF NOT EXISTS blog_operation_log (
    id           BIGSERIAL PRIMARY KEY,
    user_id      BIGINT,
    username     VARCHAR(50),
    operation    VARCHAR(100) NOT NULL,
    method       VARCHAR(200),
    params       TEXT,
    ip           VARCHAR(50),
    user_agent   VARCHAR(500),
    status       SMALLINT DEFAULT 1,
    error_msg    TEXT,
    execute_time BIGINT,
    create_time  TIMESTAMP DEFAULT NOW()
);

-- 18. OSS 文件表
CREATE TABLE IF NOT EXISTS blog_oss_file (
    id          BIGSERIAL PRIMARY KEY,
    object_key  VARCHAR(512) NOT NULL UNIQUE,
    url         VARCHAR(1024) NOT NULL,
    status      SMALLINT DEFAULT 1,
    create_time TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_blog_operation_log_user ON blog_operation_log(user_id);
CREATE INDEX IF NOT EXISTS idx_blog_operation_log_operation ON blog_operation_log(operation);
CREATE INDEX IF NOT EXISTS idx_blog_operation_log_create_time ON blog_operation_log(create_time);

-- 触发器函数：自动更新 update_time
CREATE OR REPLACE FUNCTION update_updated_time_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.update_time = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 为需要自动更新时间的表创建触发器
CREATE TRIGGER update_blog_user_updated_time BEFORE UPDATE ON blog_user FOR EACH ROW EXECUTE FUNCTION update_updated_time_column();
CREATE TRIGGER update_blog_category_updated_time BEFORE UPDATE ON blog_category FOR EACH ROW EXECUTE FUNCTION update_updated_time_column();
CREATE TRIGGER update_blog_article_updated_time BEFORE UPDATE ON blog_article FOR EACH ROW EXECUTE FUNCTION update_updated_time_column();
CREATE TRIGGER update_blog_resume_updated_time BEFORE UPDATE ON blog_resume FOR EACH ROW EXECUTE FUNCTION update_updated_time_column();
CREATE TRIGGER update_blog_role_updated_time BEFORE UPDATE ON blog_role FOR EACH ROW EXECUTE FUNCTION update_updated_time_column();
CREATE TRIGGER update_blog_system_config_updated_time BEFORE UPDATE ON blog_system_config FOR EACH ROW EXECUTE FUNCTION update_updated_time_column();

-- 存储过程：更新文章统计
CREATE OR REPLACE FUNCTION update_article_stats(p_article_id BIGINT)
RETURNS VOID AS $$
BEGIN
    UPDATE blog_article 
    SET comment_count = (
        SELECT COUNT(*) FROM blog_comment 
        WHERE article_id = p_article_id AND status = 1
    )
    WHERE id = p_article_id;
END;
$$ LANGUAGE plpgsql;

-- 触发器：评论状态变更时更新文章统计
CREATE OR REPLACE FUNCTION comment_stats_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        IF NEW.status = 1 THEN
            PERFORM update_article_stats(NEW.article_id);
        END IF;
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        IF OLD.status != NEW.status THEN
            PERFORM update_article_stats(NEW.article_id);
        END IF;
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        IF OLD.status = 1 THEN
            PERFORM update_article_stats(OLD.article_id);
        END IF;
        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER blog_comment_stats_trigger
    AFTER INSERT OR UPDATE OR DELETE ON blog_comment
    FOR EACH ROW EXECUTE FUNCTION comment_stats_trigger();

-- 视图
DROP VIEW IF EXISTS article_view;
CREATE VIEW article_view AS
SELECT 
    a.id,
    a.title,
    a.summary,
    a.cover_image,
    a.status,
    a.view_count,
    a.like_count,
    a.comment_count,
    a.create_time,
    a.update_time,
    c.name AS category_name,
    u.nickname AS author_name,
    COALESCE(string_agg(t.name, ',' ORDER BY t.name), '') AS tags
FROM blog_article a
LEFT JOIN blog_category c ON a.category_id = c.id
LEFT JOIN blog_user u ON a.author_id = u.id
LEFT JOIN blog_article_tag at ON a.id = at.article_id
LEFT JOIN blog_tag t ON at.tag_id = t.id
WHERE a.status = 1
GROUP BY a.id, c.name, u.nickname;

-- 初始数据
INSERT INTO blog_user (username, password, email, nickname, status)
VALUES ('admin', '$2a$10$N.zmdr9k7uOCQb376NoUnuTJ8iAt6Z5EHsM8lE9lBOsl7iKTVEFDa', 'admin@example.com', '管理员', 1)
ON CONFLICT (username) DO NOTHING;

INSERT INTO blog_role (code, name, description, data_scope) VALUES
('ADMIN', '管理员', '系统最高权限', 'ALL'),
('EDITOR', '编辑', '内容编辑管理', 'SELF'),
('USER', '普通用户', '基础访问权限', 'SELF')
ON CONFLICT (code) DO NOTHING;

INSERT INTO blog_permission (code, name, type, method, path, description) VALUES
('article:read', '文章阅读', 'API', 'GET', '/api/articles', '查看文章列表与详情'),
('article:write', '文章写作', 'API', 'POST', '/api/articles', '创建文章'),
('article:update', '文章更新', 'API', 'PUT', '/api/articles/{id}', '更新文章'),
('article:delete', '文章删除', 'API', 'DELETE', '/api/articles/{id}', '删除文章'),
('category:manage', '分类管理', 'API', NULL, '/api/categories', '管理分类'),
('tag:manage', '标签管理', 'API', NULL, '/api/tags', '管理标签'),
('comment:manage', '评论管理', 'API', NULL, '/api/comments', '管理评论'),
('resume:manage', '简历管理', 'API', NULL, '/api/resume', '管理简历'),
('user:manage', '用户管理', 'API', NULL, '/api/users', '管理用户'),
('role:manage', '角色管理', 'API', NULL, '/api/roles', '管理角色'),
('permission:manage', '权限管理', 'API', NULL, '/api/permissions', '管理权限'),
('oss:manage', 'OSS文件管理', 'API', NULL, '/api/admin/oss', '管理与清理OSS文件')
ON CONFLICT (code) DO NOTHING;

-- 绑定管理员用户角色
INSERT INTO blog_user_role (user_id, role_id)
SELECT u.id, r.id FROM blog_user u, blog_role r WHERE u.username = 'admin' AND r.code = 'ADMIN'
ON CONFLICT (user_id, role_id) DO NOTHING;

-- 角色-权限关系（管理员 -> 全部）
INSERT INTO blog_role_permission (role_id, permission_id)
SELECT r.id, p.id FROM blog_role r, blog_permission p WHERE r.code = 'ADMIN'
ON CONFLICT DO NOTHING;

-- 编辑 -> 内容管理权限
INSERT INTO blog_role_permission (role_id, permission_id)
SELECT r.id, p.id FROM blog_role r, blog_permission p WHERE r.code = 'EDITOR' AND p.code IN (
  'article:read','article:write','article:update','article:delete','category:manage','tag:manage','comment:manage'
)
ON CONFLICT DO NOTHING;

-- 普通用户 -> 只读
INSERT INTO blog_role_permission (role_id, permission_id)
SELECT r.id, p.id FROM blog_role r, blog_permission p WHERE r.code = 'USER' AND p.code IN ('article:read')
ON CONFLICT DO NOTHING;

-- 默认分类
INSERT INTO blog_category (name, description, parent_id, sort_order) VALUES
('技术分享', '技术相关的文章分享', 0, 1),
('前端开发', '前端技术相关文章', 1, 1),
('后端开发', '后端技术相关文章', 1, 2),
('数据库', '数据库相关文章', 1, 3),
('运维部署', '运维和部署相关文章', 1, 4),
('生活随笔', '生活感悟和随笔', 0, 2),
('项目经验', '项目开发经验分享', 0, 3)
ON CONFLICT DO NOTHING;

-- 默认标签
INSERT INTO blog_tag (name, color) VALUES
('Java', '#e74c3c'),
('Spring Boot', '#3498db'),
('Vue.js', '#2ecc71'),
('PostgreSQL', '#6c9bd1'),
('Redis', '#e67e22'),
('Docker', '#9b59b6'),
('Linux', '#34495e'),
('JavaScript', '#f1c40f'),
('TypeScript', '#1abc9c'),
('MyBatis', '#e74c3c')
ON CONFLICT DO NOTHING;

-- 示例文章（author_id = 1 假设为 admin）
INSERT INTO blog_article (title, content, summary, category_id, author_id, status, view_count)
VALUES (
  '欢迎来到我的博客',
  '# 欢迎来到我的博客\n\n这是我的第一篇博客文章，在这里我将分享我的技术心得、项目经验和生活感悟。\n\n## 关于我\n\n我是一名全栈开发工程师，专注于Java后端开发和Vue.js前端开发。\n\n## 技术栈\n\n- **后端**: Java 21, Spring Boot, MyBatis-Plus\n- **前端**: Vue 3, TypeScript, Ant Design Vue\n- **数据库**: PostgreSQL, Redis\n- **部署**: Docker, Nginx\n\n期待与大家交流学习！',
  '欢迎来到我的个人博客，这里将分享技术文章和个人经验。',
  1, 1, 1, 0
)
ON CONFLICT DO NOTHING;

-- 示例简历与关联数据
INSERT INTO blog_resume (user_id, name, title, summary, email, location, github)
VALUES (1, '张三', '全栈开发工程师', '我是一名经验丰富的全栈开发工程师，拥有5年的开发经验。专注于Java后端开发和Vue.js前端开发，熟悉微服务架构和DevOps实践。', 'zhangsan@example.com', '北京', 'https://github.com/zhangsan')
ON CONFLICT (user_id) DO NOTHING;

INSERT INTO blog_work_experience (resume_id, company, position, start_date, end_date, current, description, achievements) VALUES
(1, '某互联网公司', '高级Java开发工程师', '2022-01-01', NULL, 1, '负责公司核心业务系统的开发和维护，参与系统架构设计和技术选型。', '1. 优化系统性能，提升响应速度30%\n2. 重构核心模块，提高代码可维护性\n3. 指导初级开发人员，提升团队整体技术水平'),
(1, '某软件公司', 'Java开发工程师', '2020-03-01', '2021-12-31', 0, '参与企业级应用开发，负责后端接口开发和数据库设计。', '1. 完成多个企业级项目的开发\n2. 建立代码规范，提高开发效率\n3. 参与技术分享，提升团队技术氛围')
ON CONFLICT DO NOTHING;

INSERT INTO blog_skill (resume_id, name, category, level, description) VALUES
(1, 'Java', '后端开发', 5, '熟练掌握Java核心语法和JVM调优'),
(1, 'Spring Boot', '后端开发', 4, '熟练使用Spring Boot框架开发Web应用'),
(1, 'PostgreSQL', '数据库', 4, '熟练使用PostgreSQL数据库设计和优化'),
(1, 'Redis', '数据库', 3, '了解Redis缓存和分布式锁的使用'),
(1, 'Vue.js', '前端开发', 4, '熟练使用Vue.js开发前端应用'),
(1, 'TypeScript', '前端开发', 3, '了解TypeScript类型系统'),
(1, 'Docker', '运维部署', 3, '了解Docker容器化部署'),
(1, 'Linux', '运维部署', 3, '熟悉Linux系统操作和Shell脚本')
ON CONFLICT DO NOTHING;

INSERT INTO blog_project (resume_id, name, description, technologies, github_url, demo_url, start_date, end_date) VALUES
(1, '个人博客系统', '基于Spring Boot + Vue.js的个人博客系统，支持文章管理、评论、分类标签等功能。', 'Spring Boot, Vue.js, PostgreSQL, Redis', 'https://github.com/zhangsan/blog', 'https://blog.example.com', '2023-01-01', '2023-03-01'),
(1, '电商管理系统', '企业级电商后台管理系统，包含商品管理、订单管理、用户管理、统计分析等功能。', 'Spring Boot, MyBatis-Plus, Vue.js, Element UI', 'https://github.com/zhangsan/ecommerce', NULL, '2022-06-01', '2022-12-01')
ON CONFLICT DO NOTHING;

-- 示例教育背景
INSERT INTO blog_education (resume_id, school, major, degree, start_date, end_date, current, gpa, description, achievements) VALUES
(1, '北京大学', '计算机科学与技术', '学士', '2016-09-01', '2020-06-30', 0, '3.8/4.0', '主修计算机科学与技术，学习了数据结构、算法、操作系统、数据库等核心课程。', '1. 获得校级优秀学生奖学金\n2. 参与ACM程序设计竞赛获得省级三等奖\n3. 毕业设计获得优秀等级'),
(1, '清华大学', '软件工程', '硕士', '2020-09-01', '2022-06-30', 0, '3.9/4.0', '深入学习软件工程理论与实践，专注于分布式系统和微服务架构研究。', '1. 发表学术论文2篇\n2. 获得研究生国家奖学金\n3. 参与导师科研项目，负责系统架构设计')
ON CONFLICT DO NOTHING;

-- 添加外键约束（提高数据完整性）
ALTER TABLE blog_article ADD CONSTRAINT fk_blog_article_category FOREIGN KEY (category_id) REFERENCES blog_category(id) ON DELETE SET NULL;
ALTER TABLE blog_article ADD CONSTRAINT fk_blog_article_author FOREIGN KEY (author_id) REFERENCES blog_user(id) ON DELETE CASCADE;
ALTER TABLE blog_article_tag ADD CONSTRAINT fk_blog_article_tag_article FOREIGN KEY (article_id) REFERENCES blog_article(id) ON DELETE CASCADE;
ALTER TABLE blog_article_tag ADD CONSTRAINT fk_blog_article_tag_tag FOREIGN KEY (tag_id) REFERENCES blog_tag(id) ON DELETE CASCADE;
ALTER TABLE blog_comment ADD CONSTRAINT fk_blog_comment_article FOREIGN KEY (article_id) REFERENCES blog_article(id) ON DELETE CASCADE;
ALTER TABLE blog_comment ADD CONSTRAINT fk_blog_comment_user FOREIGN KEY (user_id) REFERENCES blog_user(id) ON DELETE SET NULL;
ALTER TABLE blog_resume ADD CONSTRAINT fk_blog_resume_user FOREIGN KEY (user_id) REFERENCES blog_user(id) ON DELETE CASCADE;
ALTER TABLE blog_work_experience ADD CONSTRAINT fk_blog_work_experience_resume FOREIGN KEY (resume_id) REFERENCES blog_resume(id) ON DELETE CASCADE;
ALTER TABLE blog_skill ADD CONSTRAINT fk_blog_skill_resume FOREIGN KEY (resume_id) REFERENCES blog_resume(id) ON DELETE CASCADE;
ALTER TABLE blog_project ADD CONSTRAINT fk_blog_project_resume FOREIGN KEY (resume_id) REFERENCES blog_resume(id) ON DELETE CASCADE;
ALTER TABLE blog_education ADD CONSTRAINT fk_blog_education_resume FOREIGN KEY (resume_id) REFERENCES blog_resume(id) ON DELETE CASCADE;
ALTER TABLE blog_user_role ADD CONSTRAINT fk_blog_user_role_user FOREIGN KEY (user_id) REFERENCES blog_user(id) ON DELETE CASCADE;
ALTER TABLE blog_user_role ADD CONSTRAINT fk_blog_user_role_role FOREIGN KEY (role_id) REFERENCES blog_role(id) ON DELETE CASCADE;
ALTER TABLE blog_role_permission ADD CONSTRAINT fk_blog_role_permission_role FOREIGN KEY (role_id) REFERENCES blog_role(id) ON DELETE CASCADE;
ALTER TABLE blog_role_permission ADD CONSTRAINT fk_blog_role_permission_permission FOREIGN KEY (permission_id) REFERENCES blog_permission(id) ON DELETE CASCADE;
ALTER TABLE blog_operation_log ADD CONSTRAINT fk_blog_operation_log_user FOREIGN KEY (user_id) REFERENCES blog_user(id) ON DELETE SET NULL;

-- 添加检查约束
ALTER TABLE blog_user ADD CONSTRAINT chk_blog_user_status CHECK (status IN (0, 1));
ALTER TABLE blog_category ADD CONSTRAINT chk_blog_category_status CHECK (status IN (0, 1));
ALTER TABLE blog_article ADD CONSTRAINT chk_blog_article_status CHECK (status IN (0, 1, 2));
ALTER TABLE blog_comment ADD CONSTRAINT chk_blog_comment_status CHECK (status IN (0, 1, 2));
ALTER TABLE blog_work_experience ADD CONSTRAINT chk_blog_work_experience_current CHECK (current IN (0, 1));
ALTER TABLE blog_education ADD CONSTRAINT chk_blog_education_current CHECK (current IN (0, 1));
ALTER TABLE blog_skill ADD CONSTRAINT chk_blog_skill_level CHECK (level >= 1 AND level <= 5);
ALTER TABLE blog_role ADD CONSTRAINT chk_blog_role_status CHECK (status IN (0, 1));
ALTER TABLE blog_role ADD CONSTRAINT chk_blog_role_data_scope CHECK (data_scope IN ('ALL', 'SELF'));
ALTER TABLE blog_permission ADD CONSTRAINT chk_blog_permission_status CHECK (status IN (0, 1));
ALTER TABLE blog_permission ADD CONSTRAINT chk_blog_permission_type CHECK (type IN ('API', 'MENU'));
ALTER TABLE blog_oss_file ADD CONSTRAINT chk_blog_oss_file_status CHECK (status IN (0, 1));
ALTER TABLE blog_operation_log ADD CONSTRAINT chk_blog_operation_log_status CHECK (status IN (0, 1));

-- 创建一些有用的函数
CREATE OR REPLACE FUNCTION get_article_tags(article_id BIGINT)
RETURNS TEXT AS $$
DECLARE
    tags_result TEXT;
BEGIN
    SELECT string_agg(t.name, ',' ORDER BY t.name) INTO tags_result
    FROM blog_tag t
    JOIN blog_article_tag at ON t.id = at.tag_id
    WHERE at.article_id = $1;
    
    RETURN COALESCE(tags_result, '');
END;
$$ LANGUAGE plpgsql;

-- 创建全文搜索函数
CREATE OR REPLACE FUNCTION search_articles(search_term TEXT)
RETURNS TABLE(
    id BIGINT,
    title VARCHAR(200),
    summary VARCHAR(500),
    rank REAL
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        a.id,
        a.title,
        a.summary,
        ts_rank(to_tsvector('english', a.title || ' ' || COALESCE(a.content, '')), plainto_tsquery('english', search_term)) as rank
    FROM blog_article a
    WHERE a.status = 1
    AND to_tsvector('english', a.title || ' ' || COALESCE(a.content, '')) @@ plainto_tsquery('english', search_term)
    ORDER BY rank DESC, a.create_time DESC;
END;
$$ LANGUAGE plpgsql;

SELECT 'PostgreSQL (blog_ 前缀) 数据库初始化完成！' AS message;


